package com.ys.otms.utils;

import com.ys.otms.dao.IShipDao;
import com.ys.otms.dto.ShipInfoDto;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class OperExcel4ShipUtil {

    private static Logger logger = LoggerFactory.getLogger(OperExcel4ShipUtil.class);

    private static final int CELL_NUMS = 4;

    public static List<ShipInfoDto> readExcel(String filePath) throws Exception {

        InputStream is = new FileInputStream(filePath);
        Workbook wb= new XSSFWorkbook(is);
        Sheet sheet = wb.getSheetAt(0);
        Row row = null;
        Cell cell = null;
        List<ShipInfoDto> list = new ArrayList<ShipInfoDto>();
        for (int i = 1; i<=sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            ShipInfoDto shipInfo = new ShipInfoDto();

            cell = row.getCell(0);
            if(cell == null){
                continue;
            }
            else{
                if(StringUtil.isEmpty((String) valueType(cell))){
                    continue;
                }
            }
            shipInfo.setShipName(valueType(cell).toString());

            cell = row.getCell(1);
            if(cell == null){
                continue;
            }
            else{
                if(StringUtil.isEmpty((String) valueType(cell))){
                    continue;
                }
            }
            shipInfo.setPhoneNum(valueType(cell).toString());

            cell = row.getCell(2);
            if(cell == null){
                continue;
            }else{
                if(StringUtil.isEmpty((String) valueType(cell))){
                    continue;
                }
            }
            shipInfo.setTonnage(Double.parseDouble(valueType(cell).toString()));

            cell = row.getCell(3);
            if(cell == null){
                continue;
            }
            else{
                if(StringUtil.isEmpty((String) valueType(cell))){
                    continue;
                }
            }
            shipInfo.setMmsi(valueType(cell).toString());

            addList(list, shipInfo);
        }
        return list;
    }

    private static void addList(List<ShipInfoDto> list, ShipInfoDto shipInfo) {
        Boolean flag = false;
        for (ShipInfoDto shipInfoDto : list) {
            if (shipInfoDto.getShipName().equals(shipInfo.getShipName())) {
                flag = true;
                break;
            }
            if(shipInfoDto.getPhoneNum().equals(shipInfo.getPhoneNum())) {
                flag = true;
                break;
            }
            if (shipInfoDto.getMmsi().equals(shipInfo.getMmsi())) {
                flag = true;
                break;
            }
        }
        if(!flag) {
            list.add(shipInfo);
        }
    }

    private static Object valueType(Cell cell){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String
        // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("0");// 格式化数字
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                // System.out.println(i + "行" + j + " 列 is String type");
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle()
                        .getDataFormatString())) {
                    value = nf.format(cell.getNumericCellValue());
                } else {
                    value = sdf.format(HSSFDateUtil.getJavaDate(cell
                            .getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK://空格，空白
                value = "";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }
}
